package com.igeek.javase.ch06.jdbc.dao;

import com.igeek.javase.ch06.jdbc.entity.Girl;
import com.igeek.javase.ch06.jdbc.utils.JDBCUtils;
import com.igeek.javase.ch06.jdbc.utils.JDBCUtilsByC3P0;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
 *@author wangpeng
 *2023/7/19 16:37
 *@description
 */
//public class GirlDao {
//    //1.通过ID查询女生信息
//    public Girl selectOneById(int id) throws ClassNotFoundException, SQLException {
//        ///1.加载驱动类
//        Class.forName("com.mysql.cj.jdbc.Driver");
//        //2.获取连接对象
//        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai" ,
//                "root" , "094115");
//
//        //3.编写sql，预编译，获取语句对象
//        String sql = "select * from girl where id = ?";
//        PreparedStatement ppst = connection.prepareStatement(sql);
//        //4.处理?占位符  第一个参数：?的位置  第二个参数：数据
//        ppst.setInt(1 , id);
//        //5.执行查询，获取结果集
//        ResultSet rs = ppst.executeQuery();
//
//        //6.处理结果集
//        Girl girl = null;
//        while (rs.next()){
//            int ids = rs.getInt("id");
//            String name = rs.getString("name");
//            String sex = rs.getString("sex");
//            Date borndate = rs.getDate("borndate");
//            String phone = rs.getString("phone");
//            int boyfriendId = rs.getInt("boyfriend_id");
//            girl = new Girl(ids , name , sex , borndate , phone , null , boyfriendId);
//        }
//        //7.关闭资源
//        if(rs!=null){
//            rs.close();
//        }
//        if(ppst!=null){
//            ppst.close();
//        }
//        if(connection!=null){
//            connection.close();
//        }
//        return girl;
//    }
//
//    //2.根据名字模糊查询所有女生信息
//    public List<Girl> selectAllByName(String name) throws SQLException {
//        List<Girl> list = new ArrayList<>();
//        Connection conn= JDBCUtils.getConn();
//        //2.获取语句对象
//        String sql = "select * from girl where name like concat('%',?,'%')";
//        PreparedStatement ppst = conn.prepareStatement(sql);
//        //3.给?占位符传值
//        ppst.setString(1 , name);
//        //4.执行查询
//        ResultSet rs = ppst.executeQuery();
//        //5.处理结果集
//        while (rs.next()){
//            int ids = rs.getInt("id");
//            String names = rs.getString("name");
//            String sex = rs.getString("sex");
//            Date borndate = rs.getDate("borndate");
//            String phone = rs.getString("phone");
//            int boyfriendId = rs.getInt("boyfriend_id");
//            Girl girl = new Girl(ids , names , sex , borndate , phone , null , boyfriendId);
//            list.add(girl);
//        }
//        //6.关闭资源
//        JDBCUtils.close(conn , ppst , rs);
//        return list;
//    }
//
//    //4.根据女生的姓名更新手机号
//    public int updateGirl(String name , String phone) throws SQLException {
//        //1.获取连接对象
//        Connection conn = JDBCUtilsByC3P0.getConn();
//        //2.获取语句对象
//        String sql = "update girl set phone = ? where name = ?";
//        PreparedStatement ppst = conn.prepareStatement(sql);
//        //3.处理?占位符
//        ppst.setString(1 , phone);
//        ppst.setString(2 , name);
//        //4.执行更新
//        int i = ppst.executeUpdate();
//        //5.关闭资源
//        JDBCUtilsByC3P0.close(conn , ppst , null);
//        return i;
//    }
////    //3.添加女生信息
////    public Girl addGirlInfo(Girl girl) throws SQLException {
////        List<Girl> list = new ArrayList<>();
////        //1.获取连接对象
////        Connection conn = JDBCUtils.getConn();
////        //2.获取语句对象
////        String sql = "insert into girl values(null , '小绿' , DEFAULT , '2000-09-09' , '12312312311' , null , 6);";
////        PreparedStatement ppst = conn.prepareStatement(sql);
////        //3.给占位符传值
////        ppst.setString(1,String.valueOf(girl));
////        //4.执行查询
////        ResultSet rs = ppst.executeQuery();
////        //5.处理结果集
////        Girl girls = null;
////        while (rs.next()){
////            int ids = rs.getInt("id");
////            String names = rs.getString("name");
////            String sex = rs.getString("sex");
////            Date borndate = rs.getDate("borndate");
////            String phone = rs.getString("phone");
////            int boyfriendId = rs.getInt("boyfriend_id");
////            girls = new Girl(ids , names , sex , borndate , phone , null , boyfriendId);
////            list.add(girls);
////        }
////        //6.关闭资源
////        JDBCUtils.close(conn , ppst , rs);
////        return girl;
////    }
////    //5.通过ID删除女生信息
////    public Girl deleteOneById(int id) throws SQLException {
////        //1.获取连接对象
////        Connection conn = JDBCUtils.getConn();
////        //2.获取语句对象
////        String sql = "delete from girl where id = ?";
////        PreparedStatement ppst = conn.prepareStatement(sql);
////        //3.给?占位符传值
////        ppst.setInt(1,id);
////        //4.执行查询
////        ResultSet rs = ppst.executeQuery();
////
////        //5.处理结果集
////        Girl girl = null;
////        while (rs.next()){
////            int ids = rs.getInt("id");
////            String name = rs.getString("name");
////            String sex = rs.getString("sex");
////            Date borndate = rs.getDate("borndate");
////            String phone = rs.getString("phone");
////            int boyfriendId = rs.getInt("boyfriend_id");
////            girl = new Girl(ids , name , sex , borndate , phone , null , boyfriendId);
////        }
////
////        //6.关闭资源
////        if(rs!=null){
////            rs.close();
////        }
////        if(ppst!=null){
////            ppst.close();
////        }
////        if(conn!=null){
////            conn.close();
////        }
////        return girl;
////    }
//
//
//
//    //6.根据手机号查询女孩信息
//    public Girl selectOneByPhone(String phone) throws SQLException {
//
//        //1.获取连接对象
//        Connection conn = JDBCUtils.getConn();
//        //2.获取语句对象
//       String sql = "select * from girl where phone = ?";
//        PreparedStatement ppst = conn.prepareStatement(sql);
//        //3.给?占位符传值
//        ppst.setString(1,phone);
//        //4.执行查询
//        ResultSet rs = ppst.executeQuery();
//
//        //5.处理结果集
//        Girl girl = null;
//        while (rs.next()){
//            int ids = rs.getInt("id");
//            String name = rs.getString("name");
//            String sex = rs.getString("sex");
//            Date borndate = rs.getDate("borndate");
//            String phones = rs.getString("phone");
//            int boyfriendId = rs.getInt("boyfriend_id");
//            girl = new Girl(ids , name , sex , borndate , phone , null , boyfriendId);
//        }
//        //6.关闭资源
//        if(rs!=null){
//            rs.close();
//        }
//        if(ppst!=null){
//            ppst.close();
//        }
//        if(conn!=null){
//            conn.close();
//        }
//        return girl;
//    }
//}


public class GirlDao extends BaseDao<Girl> {
    //1.通过ID查询女生信息
    public Girl selectOneById(int id) throws ClassNotFoundException, SQLException {
        ///1.加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai" ,
                "root" , "094115");

        //3.编写sql，预编译，获取语句对象
        String sql = "select * from girl where id = ?";
        PreparedStatement ppst = connection.prepareStatement(sql);
        //4.处理?占位符  第一个参数：?的位置  第二个参数：数据
        ppst.setInt(1 , id);
        //5.执行查询，获取结果集
        ResultSet rs = ppst.executeQuery();

        //6.处理结果集
        Girl girl = null;
        while (rs.next()){
            int ids = rs.getInt("id");
            String name = rs.getString("name");
            String sex = rs.getString("sex");
            Date borndate = rs.getDate("borndate");
            String phone = rs.getString("phone");
            int boyfriendId = rs.getInt("boyfriend_id");
            girl = new Girl(ids , name , sex , borndate , phone , null , boyfriendId);
        }
        //7.关闭资源
        if(rs!=null){
            rs.close();
        }
        if(ppst!=null){
            ppst.close();
        }
        if(connection!=null){
            connection.close();
        }
        return girl;
    }

    //2.根据名字模糊查询所有女生信息
    public List<Girl> selectAllByName(String name) throws SQLException {
        List<Girl> list = new ArrayList<>();
        Connection conn= JDBCUtils.getConn();
        //2.获取语句对象
        String sql = "select * from girl where name like concat('%',?,'%')";
        PreparedStatement ppst = conn.prepareStatement(sql);
        //3.给?占位符传值
        ppst.setString(1 , name);
        //4.执行查询
        ResultSet rs = ppst.executeQuery();
        //5.处理结果集
        while (rs.next()){
            int ids = rs.getInt("id");
            String names = rs.getString("name");
            String sex = rs.getString("sex");
            Date borndate = rs.getDate("borndate");
            String phone = rs.getString("phone");
            int boyfriendId = rs.getInt("boyfriend_id");
            Girl girl = new Girl(ids , names , sex , borndate , phone , null , boyfriendId);
            list.add(girl);
        }
        //6.关闭资源
        JDBCUtils.close(conn , ppst , rs);
        return list;
    }

    //4.根据女生的姓名更新手机号
    public int updateGirl(String name , String phone) throws SQLException {
        //1.获取连接对象
        Connection conn = JDBCUtilsByC3P0.getConn();
        //2.获取语句对象
        String sql = "update girl set phone = ? where name = ?";
        PreparedStatement ppst = conn.prepareStatement(sql);
        //3.处理?占位符
        ppst.setString(1 , phone);
        ppst.setString(2 , name);
        //4.执行更新
        int i = ppst.executeUpdate();
        //5.关闭资源
        JDBCUtilsByC3P0.close(conn , ppst , null);
        return i;
    }
//    //3.添加女生信息
//    public Girl addGirlInfo(Girl girl) throws SQLException {
//        List<Girl> list = new ArrayList<>();
//        //1.获取连接对象
//        Connection conn = JDBCUtils.getConn();
//        //2.获取语句对象
//        String sql = "insert into girl values(null , '小绿' , DEFAULT , '2000-09-09' , '12312312311' , null , 6);";
//        PreparedStatement ppst = conn.prepareStatement(sql);
//        //3.给占位符传值
//        ppst.setString(1,String.valueOf(girl));
//        //4.执行查询
//        ResultSet rs = ppst.executeQuery();
//        //5.处理结果集
//        Girl girls = null;
//        while (rs.next()){
//            int ids = rs.getInt("id");
//            String names = rs.getString("name");
//            String sex = rs.getString("sex");
//            Date borndate = rs.getDate("borndate");
//            String phone = rs.getString("phone");
//            int boyfriendId = rs.getInt("boyfriend_id");
//            girls = new Girl(ids , names , sex , borndate , phone , null , boyfriendId);
//            list.add(girls);
//        }
//        //6.关闭资源
//        JDBCUtils.close(conn , ppst , rs);
//        return girl;
//    }
//    //5.通过ID删除女生信息
//    public Girl deleteOneById(int id) throws SQLException {
//        //1.获取连接对象
//        Connection conn = JDBCUtils.getConn();
//        //2.获取语句对象
//        String sql = "delete from girl where id = ?";
//        PreparedStatement ppst = conn.prepareStatement(sql);
//        //3.给?占位符传值
//        ppst.setInt(1,id);
//        //4.执行查询
//        ResultSet rs = ppst.executeQuery();
//
//        //5.处理结果集
//        Girl girl = null;
//        while (rs.next()){
//            int ids = rs.getInt("id");
//            String name = rs.getString("name");
//            String sex = rs.getString("sex");
//            Date borndate = rs.getDate("borndate");
//            String phone = rs.getString("phone");
//            int boyfriendId = rs.getInt("boyfriend_id");
//            girl = new Girl(ids , name , sex , borndate , phone , null , boyfriendId);
//        }
//
//        //6.关闭资源
//        if(rs!=null){
//            rs.close();
//        }
//        if(ppst!=null){
//            ppst.close();
//        }
//        if(conn!=null){
//            conn.close();
//        }
//        return girl;
//    }


    public int insertGirl(Girl girl) throws SQLException {
        String sql = "insert into girl values(?,?,?,?,?,?,?)";
        int i = this.update(JDBCUtilsByC3P0.getConn(),sql,girl.getId(),girl.getName(),girl.getSex(),girl.getBorndate(),girl.getPhone(),girl.getPhoto(),girl.getBoyfriendId());
        return i;
    }

    //查询名字中含有“小”字的女生个数
    public List<Girl> selectGirlByName(String name) throws SQLException {
        String sql = "select count(*) from girl where girl.name like concat('%',?,'%')";
        List<Girl> girl  = this.selectAll(JDBCUtilsByC3P0.getConn(),sql,Girl.class,name);
        return girl;
    }


    //6.根据手机号查询女孩信息
    public Girl selectOneByPhone(String phone) throws SQLException {

        //1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        //2.获取语句对象
        String sql = "select * from girl where phone = ?";
        PreparedStatement ppst = conn.prepareStatement(sql);
        //3.给?占位符传值
        ppst.setString(1,phone);
        //4.执行查询
        ResultSet rs = ppst.executeQuery();

        //5.处理结果集
        Girl girl = null;
        while (rs.next()){
            int ids = rs.getInt("id");
            String name = rs.getString("name");
            String sex = rs.getString("sex");
            Date borndate = rs.getDate("borndate");
            String phones = rs.getString("phone");
            int boyfriendId = rs.getInt("boyfriend_id");
            girl = new Girl(ids , name , sex , borndate , phone , null , boyfriendId);
        }
        //6.关闭资源
        if(rs!=null){
            rs.close();
        }
        if(ppst!=null){
            ppst.close();
        }
        if(conn!=null){
            conn.close();
        }
        return girl;
    }


}
